package com.bijie.ent_huaxiang

import java.util.Properties

import org.apache.spark.sql.{SaveMode, SparkSession}

object bijie_company_person_info {


    def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession
          .builder()
          .master("local[*]")
          .appName("JDBCRead")
          .getOrCreate()


        // 专用的读
        val url = "jdbc:mysql://192.168.1.90:3306/cy_analysis_data"
        val table1 = "company_base_info"
        val table2 = "company_person_info"
        //company_name|turn_over|total_assets|production_value|time|identification|


        val props: Properties = new Properties()
        props.setProperty("user", "root")
        props.setProperty("password", "bigData@123")

        spark.read.jdbc(url, table1, props).createOrReplaceTempView("t1")

        spark.sql(
            """
              |select
              | company_name,
              | identification
              |from t1
              |
              |
            """.stripMargin).createOrReplaceTempView("t2") //todo 三千企业 三千id
        //-----------------------------------------------------------------------------------------------
        //2015
        spark.sql(
            """
              |select
              |company_name,
              |'张一' name,
              |'广东省' province,
              |'广州市' city,
              |'广东省' native_place,
              |'群众' politics,
              |'普工' position,
              |'男' sex,
              |'未婚' marital_status,
              |'1999-10-10' bir_date,
              |'13077788899' phone,
              |'本科' education_background,
              |'广东大学' graduate_school,
              |'2005-10-10' entry_work,
              |'2015-10-10' out_work,
              |'广东省广州市' home_add,
              |identification,
              |cast(round(rand()*5) as decimal(2,0)) as work_year
              |from t2
              |
              |
            """.stripMargin).createOrReplaceTempView("t11")

        //2016
        spark.sql(
            """
              |select
              |company_name,
              |'张二' name,
              |'广东省' province,
              |'深圳市' city,
              |'广东省' native_place,
              |'群众' politics,
              |'普工' position,
              |'女' sex,
              |'未婚' marital_status,
              |'1998-10-10' bir_date,
              |'13077788899' phone,
              |'本科' education_background,
              |'广东大学' graduate_school,
              |'2004-10-10' entry_work,
              |'2014-10-10' out_work,
              |'广东省深圳市' home_add,
              |identification,
              |cast(round(rand()*5) as decimal(2,0)) as work_year
              |from t2
              |
              |
            """.stripMargin).createOrReplaceTempView("t12")


        //2017
        spark.sql(
            """
              |select
              |company_name,
              |'张三' name,
              |'贵州省' province,
              |'毕节市' city,
              |'贵州省' native_place,
              |'群众' politics,
              |'普工' position,
              |'女' sex,
              |'未婚' marital_status,
              |'1997-10-10' bir_date,
              |'13057788899' phone,
              |'本科' education_background,
              |'贵州大学' graduate_school,
              |'2003-10-10' entry_work,
              |'2013-10-10' out_work,
              |'贵州省毕节市' home_add,
              |identification,
              |cast(round(rand()*5) as decimal(2,0)) as work_year
              |from t2
              |
              |
            """.stripMargin).createOrReplaceTempView("t13")

        //2018
        spark.sql(
            """
              |select
              |company_name,
              |'张四' name,
              |'贵州省' province,
              |'毕节市' city,
              |'贵州省' native_place,
              |'群众' politics,
              |'普工' position,
              |'男' sex,
              |'未婚' marital_status,
              |'1998-10-10' bir_date,
              |'13017788899' phone,
              |'本科' education_background,
              |'四川大学' graduate_school,
              |'2002-10-10' entry_work,
              |'2012-10-10' out_work,
              |'贵州省毕节市' home_add,
              |identification,
              |cast(round(rand()*5) as decimal(2,0)) as work_year
              |from t2
              |
              |
              |
            """.stripMargin).createOrReplaceTempView("t14")


        //2019
        spark.sql(
            """
              |select
              |company_name,
              |'张五' name,
              |'河南省' province,
              |'长垣市' city,
              |'河南省' native_place,
              |'群众' politics,
              |'普工' position,
              |'男' sex,
              |'未婚' marital_status,
              |'1991-10-10' bir_date,
              |'13011788899' phone,
              |'本科' education_background,
              |'河南大学' graduate_school,
              |'2001-10-10' entry_work,
              |'2011-10-10' out_work,
              |'贵州省毕节市' home_add,
              |identification,
              |cast(round(rand()*5) as decimal(2,0)) as work_year
              |from t2
              |
              |
              |
            """.stripMargin).createOrReplaceTempView("t15")

        //2015~2019
        spark.sql(
            """
              |select
              |company_name,
              |name,
              |province,
              |city,
              |native_place,
              |politics,
              |position,
              |sex,
              |marital_status,
              |bir_date,
              |phone,
              |education_background,
              |graduate_school,
              |entry_work,
              |out_work,
              |home_add,
              |identification,
              |work_year
              |from t11
              |
              |union all
              |select
              |company_name,
              |name,
              |province,
              |city,
              |native_place,
              |politics,
              |position,
              |sex,
              |marital_status,
              |bir_date,
              |phone,
              |education_background,
              |graduate_school,
              |entry_work,
              |out_work,
              |home_add,
              |identification,
              |work_year
              |from t12
              |
              |union all
              |select
              |company_name,
              |name,
              |province,
              |city,
              |native_place,
              |politics,
              |position,
              |sex,
              |marital_status,
              |bir_date,
              |phone,
              |education_background,
              |graduate_school,
              |entry_work,
              |out_work,
              |home_add,
              |identification,
              |work_year
              |from t13
              |
              |union all
              |select
              |company_name,
              |name,
              |province,
              |city,
              |native_place,
              |politics,
              |position,
              |sex,
              |marital_status,
              |bir_date,
              |phone,
              |education_background,
              |graduate_school,
              |entry_work,
              |out_work,
              |home_add,
              |identification,
              |work_year
              |from t14
              |
              |union all
              |select
              |company_name,
              |name,
              |province,
              |city,
              |native_place,
              |politics,
              |position,
              |sex,
              |marital_status,
              |bir_date,
              |phone,
              |education_background,
              |graduate_school,
              |entry_work,
              |out_work,
              |home_add,
              |identification,
              |work_year
              |from t15            """.stripMargin).createOrReplaceTempView("t20")

        val df = spark.sql(
            """
              |
              |select
              |1 id,
              |company_name,
              |name,
              |province,
              |city,
              |native_place,
              |politics,
              |position,
              |sex,
              |marital_status,
              |bir_date,
              |phone,
              |education_background,
              |graduate_school,
              |entry_work,
              |out_work,
              |home_add,
              |identification,
              |work_year
              |from t20
              |
              |
            """.stripMargin)



        //data.show()


       df.write.mode(SaveMode.Append).jdbc(url, table2, props) //Overwrite  会把表格重新建表，mysql注释都没了
        spark.close()
    }
}